package SQLUtils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.os.Environment;
import android.os.Looper;
import android.util.Log;
import android.widget.Toast;


import com.example.zelonghu.testgps.AppContent;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import Dialog.Cat_adopt_Dialog;
import Entities.CatAdoptList;
import Entities.ClothesDonation;
import Entities.ClothesDonationList;
import Entities.LibraryRegisterList;
import Preferences.Preferences;

/**
 * @author zelonghu
 * @name test_all_without_network
 * @class name：SQLUtils
 * @class describe
 * @time 2019/4/13 8:42 PM
 * @change
 * @chang time
 * @class sqllite相关
 */
public class sqllite {
    /**
     * @author zelonghu
     * @time 2019/6/29  9:45 AM
     * @param db 数据库实例
     * @describe 定义LibraryRegisterList
     */
    public static void  createLibraryRegisterListTable(SQLiteDatabase db){
        try {
            //创建表SQL语句

            String stu_table = "create table LibraryRegisterListTable(id integer primary key autoincrement," +
                    "Lname varchar[30]," +
                    "Longtitude real,Latitude real," +
                    "LBookNumber integer,LPerson_name varchar[10]," +
                    "LPerson_tel varchar[30],LState varchar[10])";
            //执行SQL语句
            db.execSQL(stu_table);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    /**
     * @author zelonghu
     * @time 2019/6/29  9:45 AM
     * @param db 数据库实例
     * @describe 定义CatAdoptList
     */
    public static void  createCatAdoptListTable(SQLiteDatabase db){
        try {
            //创建表SQL语句

            String stu_table = "create table CatAdoptListTable(Longtitude real ," +
                    "Latitude real," +"Cattype varchar[20] ,"+"Catid integer primary key autoincrement, "+
                    "Cathospital varchar[20],Cathealth varchar[10],CatpicPath varchar[20]," +
                    "Adoptname varchar[10],Adoptphone varchar[10]," +
                    "State varchar[10])";
            //执行SQL语句
            db.execSQL(stu_table);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    /**
     * @author zelonghu
     * @time 2019/6/29  9:45 AM
     * @param db 数据库实例
     * @describe 定义ClothesDonateList
     */
    public static void  create_ClothesDonation_ListTable(SQLiteDatabase db){
        try {
            //创建表SQL语句

            String stu_table = "create table ClothesDonationListTable(CDID integer primary key autoincrement ," +
                    "CDName varchar[20]," +"Latitude real ,"+"Longtitude real , CDStartTime varchar[10], "+
                   " CDRemark varchar[20]," +
                    "CDLID varchar[10],CDDescription varchar[50]," +
                    "CDState varchar[10],CDdonate_Phone varchar[20],CDdonate_name varchar[10],CDdonate_number integer)";
            //执行SQL语句
            db.execSQL(stu_table);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * @author zelonghu
     * @time 2019/6/29  9:53 AM
     * @param db SQLLiteDatabase 对象
     * @param lrl LibraryRegisterList实例对象
     * @describe 传入数据库实例对象
     */
    public static void insertLibraryRegisterList(SQLiteDatabase db, LibraryRegisterList lrl){
        //实例化常量值
        ContentValues cValue = new ContentValues();
        //添加数据
        cValue.put("Lname",lrl.getLname() );
        cValue.put("Longtitude",lrl.getLongtitude());
        cValue.put("Latitude",lrl.getLatitude());
        cValue.put("LBookNumber",lrl.getLBookNumber());
        cValue.put("LPerson_name",lrl.getLPerson_name());
        cValue.put("LPerson_tel",lrl.getLPerson_tel());
        cValue.put("LState",lrl.getLState());
        try {
            //调用insert()方法插入数据
            db.insert(AppContent.library_register_list_tablename, null, cValue);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    /**
     * @author zelonghu
     * @time 2019/6/29  9:53 AM
     * @param db SQLLiteDatabase 对象
     * @param cal CatAdoptList 示例对象
     * @describe 传入数据库实例对象
     */
    public static void insertCat_adopt_List(SQLiteDatabase db, CatAdoptList cal){
        //实例化常量值
        ContentValues cValue = new ContentValues();
        //添加数据
        cValue.put("Longtitude",cal.getLongtitude() );
        cValue.put("Latitude",cal.getLatitude());
        cValue.put("Cattype",cal.getCattype());
        cValue.put("Cathospital",cal.getCathospital());
        cValue.put("Cathealth",cal.getCathealth());
        cValue.put("CatpicPath",cal.getCatpicPath());
        cValue.put("Adoptname",cal.getAdoptname());
        cValue.put("Adoptphone",cal.getAdoptphone());
        cValue.put("State",cal.getState());
        try {
            //调用insert()方法插入数据
            db.insert(AppContent.cat_register_list_tablename, null, cValue);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    /**
     * @author zelonghu
     * @time 2019/6/29  9:53 AM
     * @param db SQLLiteDatabase 对象
     * @param cdl ClothesDonation 实例对象
     * @describe 传入数据库实例对象
     */
    public static void insert_Clothes_donation_List(SQLiteDatabase db, ClothesDonationList cdl){
        //实例化常量值
        ContentValues cValue = new ContentValues();
        //添加数据
        cValue.put("CDName",cdl.getCDName());
        cValue.put("Latitude",cdl.getLatitude());
        cValue.put("Longtitude",cdl.getLongtitude());
        cValue.put("CDStartTime",cdl.getCDStartTime());
        cValue.put("CDRemark",cdl.getCDRemark());
        cValue.put("CDLID",cdl.getCDLID());
        cValue.put("CDDescription",cdl.getCDDescription());
        cValue.put("CDState",cdl.getCDState());
        cValue.put("CDdonate_Phone",cdl.getCDdonate_Phone());
        cValue.put("CDdonate_name",cdl.getCDdonate_name());
        cValue.put("CDdonate_number",cdl.getCDdonate_number());
        try {
            //调用insert()方法插入数据
            db.insert(AppContent.clothes_donatioin_list_tablename, null, cValue);
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    /**
     * @author zelonghu
     * @time 2019/6/29  9:43 AM
     * @describe 获取DB
     */
    public static SQLiteDatabase getDB(Context context){
        return SQLiteDatabase.openOrCreateDatabase(context.getFilesDir().getPath()+"/sql.db",null);
    }


    /**
     *  @author zelonghu
     *  @time 2019/4/19  2:19 PM
     *  @describe drop a table
     */
    public static void droptable(SQLiteDatabase db,String tablename){
        db.execSQL("drop table " + tablename );
    }

    /**
     * @author zelonghu
     * @time 2019/4/13  9:18 PM
     * @describe 以时间顺序逆序读取所有的数据
     *              get all the SensorData into a List from the database desc
     */
    public static List<LibraryRegisterList> query_Library_Register_List_Desc(SQLiteDatabase db ) {
        List<LibraryRegisterList> sdl = new ArrayList<LibraryRegisterList>();
        try {
            //查询获得游标
            Cursor cursor = db.query(AppContent.library_register_list_tablename, null, null,
                    null, null, null, "id desc","");
            //判断游标是否为空
            if (cursor.moveToFirst()) {
                //遍历游标
                Log.i("cursor count", cursor.getCount() + "");
                while (!cursor.isAfterLast() ) {
                    LibraryRegisterList lrl = new LibraryRegisterList();
                    lrl.setLatitude(cursor.getString(1));
                    lrl.setLongtitude(cursor.getDouble(2)+"");
                    lrl.setLatitude(cursor.getDouble(3) + "");
                    lrl.setLBookNumber(cursor.getInt(4) + "");
                    lrl.setLPerson_name(cursor.getString(5));
                    lrl.setLPerson_tel(cursor.getString(6));
                    lrl.setLState(cursor.getString(7));
                    sdl.add(lrl);
                    cursor.moveToNext();
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return sdl;
    }

    /**
     * @author zelonghu
     * @time 2019/4/13  9:18 PM
     * @describe 以时间顺序逆序读取所有的数据
     *              get all the SensorData into a List from the database desc
     */
    public static List<CatAdoptList> query_Cat_adopt_List_Desc(SQLiteDatabase db ) {
        List<CatAdoptList> sdl = new ArrayList<CatAdoptList>();
        try {
            //查询获得游标
            Cursor cursor = db.query(AppContent.cat_register_list_tablename, null, null,
                    null, null, null, "Catid desc","");
            //判断游标是否为空
            if (cursor.moveToFirst()) {
                //遍历游标
                Log.i("cursor count", cursor.getCount() + "");
                while (!cursor.isAfterLast() ) {
                    CatAdoptList cal = new CatAdoptList();
                    cal.setLongtitude(cursor.getDouble(0));
                    cal.setLatitude(cursor.getDouble(1));
                    cal.setCattype(cursor.getString(2));
                    cal.setCatid(cursor.getString(3));
                    cal.setCathospital(cursor.getString(4));
                    cal.setCathealth(cursor.getString(5));
                    cal.setCatpicPath(cursor.getString(6));
                    cal.setAdoptname(cursor.getString(7));
                    cal.setAdoptphone(cursor.getString(8));
                    cal.setState(cursor.getString(9));

                    sdl.add(cal);
                    cursor.moveToNext();
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return sdl;
    }
    /**
     * @author zelonghu
     * @time 2019/4/13  9:18 PM
     * @describe 以时间顺序逆序读取所有的数据
     *              get all the SensorData into a List from the database desc
     */
    public static List<ClothesDonationList> query_Clothes_donation_List_Desc(SQLiteDatabase db ) {
        List<ClothesDonationList> sdl = new ArrayList<ClothesDonationList>();
        try {
            //查询获得游标
            Cursor cursor = db.query(AppContent.clothes_donatioin_list_tablename, null, null,
                    null, null, null, "CDID desc","");
            //判断游标是否为空
            if (cursor.moveToFirst()) {
                //遍历游标
                Log.i("cursor count", cursor.getCount() + "");
                while (!cursor.isAfterLast() ) {
                    ClothesDonationList cdl = new ClothesDonationList();

                    cdl.setCDName(cursor.getString(1));
                    cdl.setLatitude(cursor.getDouble(2));
                    cdl.setLongtitude(cursor.getDouble(3));
                    cdl.setCDStartTime(cursor.getString(4));
                    cdl.setCDRemark(cursor.getString(5));
                    cdl.setCDLID(cursor.getString(6));
                    cdl.setCDDescription(cursor.getString(7));
                    cdl.setCDState(cursor.getString(8));
                    cdl.setCDdonate_Phone(cursor.getString(9));
                    cdl.setCDName(cursor.getString(10));
                    cdl.setCDdonate_number(cursor.getInt(11));
                    Log.i("donate",cdl.toString());

                    sdl.add(cdl);
                    cursor.moveToNext();
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return sdl;
    }

    /**
     * @name sqllite
     * @class name：sqllite
     * @param db
     *        SQLite
     * @class describe
     * @author zelonghu
     * @time 2019/6/12 12:37 PM
     * @change
     * @chang time
     * @class 清空某一表
     */
    public void deleteTable(SQLiteDatabase db,String listname){
        db.execSQL("delete  from  " + listname + ";");
    }
    /**
     * @name sqllite
     * @class name：sqllite
     * @param db database
     * @param lnumber 捐书活动数量
     * @class describe
     * @author zelonghu
     * @time 2019/6/12 12:37 PM
     * @change
     * @chang time
     *
     * @class 删除Library表中的一项
     */
    public static void deleteLname(SQLiteDatabase db,String lnumber){
        db.execSQL("delete  from  " + "LibraryRegisterListTable" + " where LBookNumber = " +lnumber  + " ;");
    }
    /**
     * @name sqllite
     * @class name：sqllite
     * @param db database
     * @param cattype 猫的type
     * @class describe
     * @author zelonghu
     * @time 2019/6/12 12:37 PM
     * @change
     * @chang time
     *
     * @class 删除Catadoptlist表中的一项
     */
    public static void delete_cat_adopt_type(SQLiteDatabase db,String cattype){
        db.execSQL("delete  from  " + AppContent.cat_register_list_tablename + " where Cattype = \"" +cattype  + "\";");
    }
    /**
     * @name sqllite
     * @class name：sqllite
     * @param db database
     * @param number 捐献数量
     * @class describe
     * @author zelonghu
     * @time 2019/6/12 12:37 PM
     * @change
     * @chang time
     *
     * @class 删除clothes donation 表中的一项
     */
    public static void delete_clothes_donation_number(SQLiteDatabase db,String number){
        db.execSQL("delete from " + AppContent.clothes_donatioin_list_tablename + " where CDdonate_number =" + number);
    }
}
